﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OracleTableContrastTool
{
    class Program
    {
        static string sqlcnn_one = ConfigurationManager.AppSettings["sqlcnn_one"];
        static string sqlcnn_two = ConfigurationManager.AppSettings["sqlcnn_two"];
        static string DataTable = ConfigurationManager.AppSettings["DataTable"];
        static string DataWhere = ConfigurationManager.AppSettings["DataWhere"];
        static string PrimaryKeyName = ConfigurationManager.AppSettings["PrimaryKeyName"];
        static string PrimaryColumnNo = ConfigurationManager.AppSettings["PrimaryColumnNo"];
        static void Main(string[] args)
        {
            string[] ArrTable = DataTable.Split(';');

            Console.WriteLine($"开始对比---- {DateTime.Now}\r\n");
            foreach (var i in ArrTable)
            {
                if(i.Trim() == "")
                    continue;


                Console.WriteLine($"开始检查表:{i}\r\n");

                DataTable dtOne = new DataTable();
                DataTable dtTwo = new DataTable();

                using (IDbConnection cnn = new OracleConnection(sqlcnn_one))
                {
                    try
                    {
                        cnn.Open();
                        OracleCommand cmd = (OracleCommand)cnn.CreateCommand();
                        cmd.CommandText = $"select * from {i} t {DataWhere}";
                        OracleDataAdapter oracleData = new OracleDataAdapter(cmd);
                        oracleData.Fill(dtOne);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        cnn.Close();
                    }
                }

                using (IDbConnection cnn = new OracleConnection(sqlcnn_two))
                {
                    try
                    {
                        cnn.Open();
                        OracleCommand cmd = (OracleCommand)cnn.CreateCommand();
                        cmd.CommandText = $"select * from {i} t {DataWhere}";
                        OracleDataAdapter oracleData = new OracleDataAdapter(cmd);
                        oracleData.Fill(dtTwo);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        cnn.Close();
                    }
                }


                if (dtOne.Rows.Count < 0)
                {
                    Console.WriteLine($"表: {i} 行数为空，跳过检查！");
                    continue;
                }


                //检查是否有id字段
                if (dtOne.Columns[Convert.ToInt32(PrimaryColumnNo)].ColumnName.ToUpper() != PrimaryKeyName)
                {
                    Console.WriteLine($"表: {i} 没有主键{PrimaryKeyName}字段，跳过对比！");
                    continue;
                }


                if (dtOne.Rows.Count == dtTwo.Rows.Count)
                {
                    Console.WriteLine($"表: {i} 两边行数相等！");
                    for (int j = 0; j < dtOne.Rows.Count; j++)
                    {
                        string rowId = dtOne.Rows[j].ItemArray[Convert.ToInt32(PrimaryColumnNo)].ToString().Trim();
                        DataRow thisRow = dtOne.Rows[j];

                        DataRow rowTwo = GetRow(dtTwo,rowId);
                        if (rowTwo != null)
                        {
                            for(int n = 0; n < thisRow.ItemArray.Length; n++)
                            {
                                for (int t = 0; t < thisRow.ItemArray.Length; t++)
                                {
                                    if (n == t)
                                    {
                                        var n_val = thisRow.ItemArray[n].ToString();
                                        var t_val = rowTwo.ItemArray[t].ToString();

                                        if (n_val != t_val)
                                        {
                                            Console.WriteLine($"表: {i} 主键{PrimaryKeyName} {rowId} 的字段 {dtOne.Columns[n].ColumnName} 的值不一致！");
                                        }
                                        break;
                                    }
                                }
                            }
                        }
                        else {
                            Console.WriteLine($"表: {i} 主键{PrimaryKeyName} {rowId} 找不到！");
                        }
                    }
                }
                else {
                    Console.WriteLine($"表: {i} 两边行数不等！");

                    for (int j = 0; j < dtOne.Rows.Count; j++)
                    {
                        string rowId = dtOne.Rows[j].ItemArray[Convert.ToInt32(PrimaryColumnNo)].ToString().Trim();
                        DataRow thisRow = dtOne.Rows[j];

                        DataRow rowTwo = GetRow(dtTwo, rowId);
                        if (rowTwo != null)
                        {
                            for (int n = 0; n < thisRow.ItemArray.Length; n++)
                            {
                                for (int t = 0; t < thisRow.ItemArray.Length; t++)
                                {
                                    if (n == t)
                                    {
                                        var n_val = thisRow.ItemArray[n].ToString();
                                        var t_val = rowTwo.ItemArray[t].ToString();

                                        if (n_val != t_val)
                                        { 
                                            Console.WriteLine($"表: {i} 主键{PrimaryKeyName} {rowId} 的字段 {dtOne.Columns[n].ColumnName} 的值不一致！");
                                        }
                                        break;
                                    }
                                }
                            }
                        }
                        else
                        {
                            Console.WriteLine($"表: {i} 主键{PrimaryKeyName} {rowId} 找不到！");
                        }
                    }
                }


                Console.WriteLine($"\r\n\r\n\r\n");
            }

            Console.WriteLine($"对比完成---- {DateTime.Now}");
            Console.ReadKey();
        }

        public static DataRow GetRow(DataTable table, string id)
        {
            DataRow row = null;

            for (int j = 0; j < table.Rows.Count; j++)
            {
                string rowId = table.Rows[j].ItemArray[Convert.ToInt32(PrimaryColumnNo)].ToString().Trim();
                if (rowId == id)
                {
                    row = table.Rows[j];
                    break;
                }
            }

            return row;
        }


    }
}
